﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using SISCON.Dao;
using SISCON.Model;
using log4net;
using SISCON.Common;

namespace SISCON.Dao
{

    /// <summary>
    /// Summary description for UsersDAO
    /// </summary>
    public class UserDAO : CommonDAO
    {

        /// <summary>
        /// Autentifica un usuario dentro del sistema
        /// </summary>
        /// <param name="loginName">Nombre del usuario</param>
        /// <param name="password">Contrasenia del usuario</param>
        /// <returns>Regresa el usuario validado</returns>
        public User Authenticate(string loginName, string password)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                string sql = " select user_id, display_name, is_static, role_id, is_active from sis_user where login_name = @loginName and password=HASHBYTES('SHA1', @password) ";
                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                sqlCommand.Parameters.Add("@loginName", SqlDbType.VarChar);
                sqlCommand.Parameters["@loginName"].Value = loginName;
                sqlCommand.Parameters.Add("@password", SqlDbType.VarChar);
                sqlCommand.Parameters["@password"].Value = password;
                conn.Open();
                reader = sqlCommand.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();

                    User user = new User();
                    user.UserId = getReaderLong(reader, "user_id");
                    user.RoleId = getReaderLong(reader, "role_id");
                    user.DisplayName = getReaderString(reader, "display_name");
                    user.IsStatic = getReaderBool(reader, "is_static");
                    user.IsActive = getReaderBool(reader, "is_active");
                    return user;
                }
                else
                {
                    throw new SISCONException("El usuario y/o contraseña no es válido. Favor de verificar.");
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Obtiene todos los usuarios del sistema
        /// </summary>
        /// <returns>Lista de usuarios</returns>
        public List<User> GetAllUsers()
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;

            List<User> lstUsers = new List<User>();

            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                string sql = " SELECT * FROM sis_user ORDER BY login_name asc ";
                SqlCommand sqlCommand = new SqlCommand(sql, conn);

                reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    User user = new User();
                    user.UserId = getReaderLong(reader, "user_id");
                    user.RoleId = getReaderLong(reader, "role_id");
                    user.IsStatic = getReaderBool(reader, "is_static");
                    user.LoginName = getReaderString(reader, "login_name");
                    user.DisplayName = getReaderString(reader, "display_name");
                    user.Email = getReaderString(reader, "email");
                    user.IsActive = getReaderBool(reader, "is_active");
                    user.CreationDate = getReaderDateTime(reader, "creation_date");
                    user.LastUpdateDate = getReaderDateTime(reader, "last_update_date");

                    lstUsers.Add(user);
                }
                reader.Close();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return lstUsers;

        }

        /// <summary>
        /// Almacena un nuevo usuario dentro de la base de datos
        /// </summary>
        /// <param name="userId">Id interno del usuario</param>
        /// <param name="loginName">Nombre del usuario utilizado para iniciar sesion</param>
        /// <param name="displayName">Nombre del usuario</param>
        /// <param name="password">Contrasenia</param>
        /// <param name="email">Email del usuario</param>
        /// <param name="roleId">Id del rol asignado al usaurio</param>
        /// <param name="enabled">Indica si el usuario esta activo o inactivo</param>
        public void SaveUser(string userId, string loginName, string displayName, string password, string email,
                             string roleId, bool enabled)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                if (userId != null && !userId.Equals("0"))
                {
                    string sqlQuery = "UPDATE sis_user SET login_name=@loginName, display_name=@displayName, " +
                        (password != null && !password.Equals("U*N*C*H*A*N*G*E*D*") ? " password = HASHBYTES('SHA1', @password), " : "") +
                        " is_active = " + (enabled ? "1" : "0") + ", " +
                        " email = @email, " +
                        " role_id = @roleId " +
                        " WHERE user_id = @userId ";
                    SqlCommand sqlCommand = new SqlCommand(sqlQuery, conn);
                    sqlCommand.Parameters.Add("@loginName", SqlDbType.VarChar);
                    sqlCommand.Parameters["@loginName"].Value = loginName;
                    sqlCommand.Parameters.Add("@displayName", SqlDbType.VarChar);
                    sqlCommand.Parameters["@displayName"].Value = displayName;
                    if (password != null && !password.Equals("U*N*C*H*A*N*G*E*D*"))
                    {
                        sqlCommand.Parameters.Add("@password", SqlDbType.VarChar);
                        sqlCommand.Parameters["@password"].Value = password;
                    }
                    sqlCommand.Parameters.Add("@email", SqlDbType.VarChar);
                    sqlCommand.Parameters["@email"].Value = email;

                    sqlCommand.Parameters.Add("@roleId", SqlDbType.BigInt);
                    sqlCommand.Parameters["@roleId"].Value = long.Parse(roleId);

                    sqlCommand.Parameters.Add("@userId", SqlDbType.BigInt);
                    sqlCommand.Parameters["@userId"].Value = long.Parse(userId);

                    sqlCommand.ExecuteNonQuery();
                }
                else
                {
                    string sqlQuery = "INSERT INTO sis_user (login_name, display_name, password, email, " +
                        " role_id, is_active, created_by, creation_date, last_updated_by, last_update_date) VALUES " +
                        " (@loginName, @displayName, HASHBYTES('SHA1', @password), @email," +
                        " @roleId, 1, 1, SYSDATETIME(), 1, SYSDATETIME()) ";
                    SqlCommand sqlCommand = new SqlCommand(sqlQuery, conn);
                    sqlCommand.Parameters.Add("@loginName", SqlDbType.VarChar);
                    sqlCommand.Parameters["@loginName"].Value = loginName;
                    sqlCommand.Parameters.Add("@displayName", SqlDbType.VarChar);
                    sqlCommand.Parameters["@displayName"].Value = displayName;
                    sqlCommand.Parameters.Add("@password", SqlDbType.VarChar);
                    sqlCommand.Parameters["@password"].Value = password;
                    sqlCommand.Parameters.Add("@email", SqlDbType.VarChar);
                    sqlCommand.Parameters["@email"].Value = email;

                    sqlCommand.Parameters.Add("@roleId", SqlDbType.BigInt);
                    sqlCommand.Parameters["@roleId"].Value = long.Parse(roleId);

                    sqlCommand.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlEx)
            {
                if (sqlEx.Number == 2601)
                {
                    throw new SISCONException("El nombre de usuario ya existe. Favor de verificar");
                }
                else
                {
                    throw sqlEx;
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Elimina el usuario especificado por userId
        /// </summary>
        /// <param name="userId">Id interno del usuario</param>
        public void DeleteUser(string userId)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                string sql = "DELETE FROM sis_user WHERE user_id = @userId";
                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                sqlCommand.Parameters.Add("@userId", SqlDbType.BigInt);
                sqlCommand.Parameters["@userId"].Value = long.Parse(userId);

                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Cierra la sesion activa en el sistema indica por sessionUUID
        /// </summary>
        /// <param name="sessionUUID">Sesion del sistema</param>
        public void Logout(string sessionUUID)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                string sqlQuery = "UPDATE sis_session SET is_active = 0 where session_uuid = @sessionUUID ";
                SqlCommand sqlCommand = new SqlCommand(sqlQuery, conn);

                System.Data.SqlTypes.SqlGuid guid;
                try
                {
                    guid = System.Data.SqlTypes.SqlGuid.Parse(sessionUUID);
                }
                catch (Exception e)
                {
                    throw new SISCONException("No fue posible terminar la sesión. Favor de intentar más tarde.");
                }
                sqlCommand.Parameters.Add(getNewParameter("@sessionUUID", SqlDbType.UniqueIdentifier, 80, guid));
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Verifica si un usuario tiene acceso a un metodo del sistema
        /// </summary>
        /// <param name="SessionUUID">Sesion del sistema</param>
        /// <param name="RequiredAccessLevel">Nivel de acceso requerido para el metodo</param>
        /// <param name="RequiredRoles">Roles que que pueden acceder al metodo</param>
        /// <returns>Regresa si el usuario tiene o no acceso a ese metodo</returns>
        public bool HasAccessToResource(string SessionUUID, string RequiredAccessLevel, params string[] RequiredRoles)
        {
            SqlCommand sqlCommand = new SqlCommand();
            SqlDataReader reader = null;
            SqlConnection conn = null;
            try
            {
                if (RequiredRoles == null || RequiredRoles.Length == 0)
                {
                    throw new SISCONException("Error inesperado. No tiene acceso a este recurso.");
                }

                string roles = "(";

                for (int i = 0; i < RequiredRoles.Length; i++)
                {
                    roles += i == 0 ? " rd.resource = @requiredRole" + i + " " : " OR rd.resource = @requiredRole" + i + " ";
                }

                roles += ")";

                conn = new SqlConnection(_ConnStr);
                conn.Open();
                sqlCommand.CommandText = " select rd.resource, rd.access_level " +
                                   " from sis_session AS s LEFT OUTER JOIN " +
                                   " sis_user AS u ON u.user_id = s.user_id LEFT OUTER JOIN " + //AND u.is_active = 1 
                                   " sis_role AS r ON r.role_id = u.role_id LEFT OUTER JOIN " + //AND r.is_active = 1 
                                   " sis_role_det AS rd ON rd.role_id = r.role_id " +
                                   " where s.is_active = 1 AND s.session_uuid like @sessionUUID " +
                                   " and " + roles;

                System.Data.SqlTypes.SqlGuid guid;
                try
                {
                    guid = System.Data.SqlTypes.SqlGuid.Parse(SessionUUID);
                }
                catch (Exception e)
                {
                    throw new SISCONException("No cuenta con los permisos necesarios para la operación solicitada");
                }
                sqlCommand.Parameters.Add(getNewParameter("@sessionUUID", SqlDbType.UniqueIdentifier, 80, guid));
                for (int i = 0; i < RequiredRoles.Length; i++)
                {
                    sqlCommand.Parameters.Add(getNewParameter("@requiredRole" + i, SqlDbType.NVarChar, 50, RequiredRoles[i]));
                }
                sqlCommand.Connection = conn;

                reader = sqlCommand.ExecuteReader();

                bool hasAccess = false;

                if (!reader.Read())
                {
                    throw new SISCONException("Su sesión ha expirado. Favor de ingresar nuevamente al sistema.");
                }
                else
                {
                    do
                    {
                        if (reader["access_level"].ToString() == Role.WRITE_ACCESS)
                        {
                            hasAccess = true;
                        }
                        else if (RequiredAccessLevel == Role.READ_ACCESS)
                        {
                            if (reader["access_level"].ToString() == Role.READ_ACCESS)
                            {
                                hasAccess = true;
                            }
                        }
                    } while (reader.Read());
                }
                return hasAccess;
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Obtiene las propiedades de la sesion activa
        /// </summary>
        /// <param name="SessionUUID">Sesion del sistema</param>
        /// <returns>Usuario con las propiedas de la sesion activa</returns>
        public User GetFromSessionUUID(String SessionUUID)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                SqlCommand sqlCommand = new SqlCommand(
                    " SELECT u.*, rd.resource, rd.access_level " +
                    " FROM sis_session AS s INNER JOIN " +
                    " sis_user AS u ON (u.user_id = s.user_id) INNER JOIN " +
                    " sis_role AS r ON r.role_id = u.role_id LEFT OUTER JOIN " +
                    " sis_role_det AS rd ON rd.role_id = r.role_id " +
                    " WHERE s.session_uuid = '" + SessionUUID + "'", conn);
                conn.Open();
                reader = sqlCommand.ExecuteReader();

                User user = null;

                while (reader.Read())
                {
                    if (user == null)
                    {
                        user = new User();
                        user.UserId = getReaderLong(reader, "user_id");
                        user.DisplayName = getReaderString(reader, "display_name");
                        user.RoleId = getReaderLong(reader, "role_id");
                        user.IsStatic = getReaderBool(reader, "is_static");
                        user.IsActive = getReaderBool(reader, "is_active");
                        user.Permissions = new List<RoleDet>();
                    }
                    RoleDet roleDet = new RoleDet();
                    roleDet.Resource = getReaderString(reader, "resource");
                    roleDet.AccessLevel = getReaderString(reader, "access_level");

                    user.Permissions.Add(roleDet);
                }

                return user;

            }
            catch (SISCONException velEx)
            {
                throw velEx;
            }
            catch (Exception exp)
            {
                throw new SISCONException("No se encontró una sesión activa para este usuario.");
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
    }
}